IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id('MD3.ScriptFKConstraintTable'))
BEGIN
	DROP PROCEDURE MD3.ScriptFKConstraintTable
END
GO

CREATE Procedure MD3.ScriptFKConstraintTable

AS  
BEGIN

/*

	Generates a SQL script that can be saved to 04ForeignKeyConstraints, in MasterData format, that is used as the basis for all 
	FK constraint manipulation.



*/


PRINT '/*'
PRINT '	This script holds the MasterData about our FK Constraints'
PRINT '	Simply modify this metadata and rerun the MD3 deployment and any FK constraint changes'
PRINT '		will be reflected in your database.  '
PRINT ''
PRINT '	To add a new FK constraint...Add a new INSERT line to the file.'
PRINT '	To modify a FK constraint...Modify the values of the EXISTING check constraint'
PRINT '	To delete a FK constraint...Set the DropConstraint flag to 1.  Do not actually delete the row.'
PRINT ''
PRINT ' This script can be automatically regenerated by running MD3.ScriptFKConstraintTable'
PRINT '*/'

SET NOCOUNT ON
PRINT 'SET NOCOUNT ON'
PRINT '--remove all old entries from table'
PRINT 'TRUNCATE TABLE MD3.FKConstraints'
PRINT ''
PRINT '--add new entries to table'

DECLARE @exec_str VARCHAR(MAX), @new_str VARCHAR(MAX), @quote VARCHAR(MAX) = '''';
SELECT @exec_str = 'INSERT INTO MD3.FKConstraints (ForeignKeyName,SchemaName,TableName,KeyColumns,ParentSchemaName,ParentTableName,ParentKeyColumns,IsNotTrusted,IsDeleteCascade,IsUpdateCascade,DropFK) VALUES('

DECLARE 
	@ForeignKeyName varchar(256)
	,@SchemaName varchar(256)
	,@TableName varchar(256)
	,@KeyColumns varchar(256)
	,@ParentSchemaName varchar(256)
	,@ParentTableName varchar(256)
	,@ParentKeyColumns varchar(256)
	,@IsNotTrusted varchar(256)
	,@IsDeleteCascade varchar(256)
	,@IsUpdateCascade varchar(256)
	,@DropConstraint varchar(256)

DECLARE dave CURSOR FOR 
	SELECT 
		ForeignKeyName,SchemaName,TableName,KeyColumns,ParentSchemaName,ParentTableName,ParentKeyColumns,IsNotTrusted,IsDeleteCascade,IsUpdateCascade,DropFK
	FROM MD3.FKConstraints
	ORDER BY SchemaName, TableName
OPEN dave
FETCH NEXT FROM dave INTO 
	@ForeignKeyName 
	,@SchemaName 
	,@TableName 
	,@KeyColumns 
	,@ParentSchemaName 
	,@ParentTableName 
	,@ParentKeyColumns 
	,@IsNotTrusted 
	,@IsDeleteCascade 
	,@IsUpdateCascade 
	,@DropConstraint 
WHILE (@@FETCH_STATUS = 0)
BEGIN

	SELECT @new_str  = @exec_str + '''' + @ForeignKeyName + ''',''' + @SchemaName + ''',''' + @TableName + ''',''' + @KeyColumns + ''','''
	SELECT @new_str += @ParentSchemaName + ''',''' + @ParentTableName + ''',''' + @ParentKeyColumns + ''',' + @IsNotTrusted + ','
	SELECT @new_str += @IsDeleteCascade + ',' + @IsUpdateCascade + ',' 
	SELECT @new_str += @DropConstraint +');'
	PRINT @new_str
	
	
	FETCH NEXT FROM dave INTO 
		@ForeignKeyName 
		,@SchemaName 
		,@TableName 
		,@KeyColumns 
		,@ParentSchemaName 
		,@ParentTableName 
		,@ParentKeyColumns 
		,@IsNotTrusted 
		,@IsDeleteCascade 
		,@IsUpdateCascade 
		,@DropConstraint 
END;
CLOSE dave
DEALLOCATE dave


PRINT '--now building FK Constraints'
PRINT 'EXEC MD3.RunFKConstraints'
PRINT 'GO'
PRINT 'SET NOCOUNT OFF'

END
GO
